//
//  CacheTool.swift
//  swiftProject
//
//  Created by ZhangSai on 2021/5/27.
//

import UIKit
import SQLite
import SwiftyJSON

class CacheTool: NSObject {
    
    static let cacheTool = CacheTool()

    let path = NSSearchPathForDirectoriesInDomains(
                    .documentDirectory, .userDomainMask, true
                    ).first!
    
    private var db:Connection?
    
    private var tmsOption : Table? //配置表
    private var tmsCountryCode : Table? //手机号国家表
    private var tmsArea : Table? //城市表
    private var tmsCity : Table? //城市表
    private var tmsAddress : Table? //地址表
    private var tmsGoodsType : Table? //物品类型表
    
    func getDB() -> Connection {
        if db == nil {
            db = try! Connection(path + "/Wuliu.sqlite")
            db?.busyTimeout = 5.0
        }
        return db!
    }
    
    let key = Expression<String>("key")
    let value = Expression<String>("value")
    let loginAccount = Expression<String>("loginAccount")
    
    func getTmsOption() -> Table {
        if tmsOption == nil {
            tmsOption = Table("tmsOption")
            try! getDB().run(tmsOption!.create(temporary: false, ifNotExists: true, withoutRowid: false, block: { (tab) in
                tab.column(key,primaryKey: true)
                tab.column(loginAccount,unique:true)
                tab.column(value)
            }))
        }
        return tmsOption!;
    }
    
    let nicename = Expression<String>("nicename")
    let phonecode = Expression<String>("phonecode")
    
    func getTmsCountryCode() -> Table {
        if tmsCountryCode == nil {
            tmsCountryCode = Table("tmsCountryCode")
            try! getDB().run(tmsCountryCode!.create(temporary: false, ifNotExists: true, withoutRowid: false, block: { (tab) in
                tab.column(nicename,unique:true)
                tab.column(phonecode,primaryKey:true)
            }))
        }
        return tmsCountryCode!;
    }
    
    let ID = Expression<String>("ID")
    let name = Expression<String>("name")
    
    func getTmsArea() -> Table {
        if tmsArea == nil {
            tmsArea = Table("tmsArea")
            try! getDB().run(tmsArea!.create(temporary: false, ifNotExists: true, withoutRowid: false, block: { (tab) in
                tab.column(id,primaryKey: true)
                tab.column(name)
            }))
        }
        return tmsArea!;
    }
    
    let parentID = Expression<Int>("parentID")
    let parentName = Expression<String>("parentName")
    
    func getTmsCity() -> Table {
        if tmsCity == nil {
            tmsCity = Table("tmsCity")
            try! getDB().run(tmsCity!.create(temporary: false, ifNotExists: true, withoutRowid: false, block: { (tab) in
                tab.column(id,primaryKey: true)
                tab.column(name)
                tab.column(parentID)
                tab.column(parentName)
            }))
        }
        return tmsCity!;
    }

    let isDefault = Expression<Int>("isDefault")
    let regionId = Expression<Int>("regionId")
    let cityId = Expression<Int>("cityId")
    let id = Expression<Int>("id")
    let province = Expression<String>("parentName")
    let city = Expression<String>("city")
    let detailAddress = Expression<String>("detailAddress")
    let phone = Expression<String>("phone")
    let phoneCode = Expression<String>("phoneCode")
    let postCode = Expression<String>("postCode")
    let region = Expression<String>("region")
    let status = Expression<String>("status")
    let updateTime = Expression<String>("updateTime")
    let modifTimer = Expression<String>("modifTimer")

    func getTmsAddress() -> Table {
        if tmsAddress == nil {
            tmsAddress = Table("tmsAddress")
            try! getDB().run(tmsAddress!.create(temporary: false, ifNotExists: true, withoutRowid: false, block: { (tab) in
                tab.column(id,primaryKey: true)
                tab.column(province)
                tab.column(city)
                tab.column(cityId)
                tab.column(region)
                tab.column(regionId)
                tab.column(detailAddress)
                tab.column(isDefault)
                tab.column(name)
                tab.column(phone)
                tab.column(phoneCode)
                tab.column(postCode)
                tab.column(status)
                tab.column(updateTime)
                tab.column(modifTimer)
            }))
        }
        return tmsAddress!;
    }
    
    func insterAddressList(addressList:Array<AddressModel>)  {
        try! getDB().run(getTmsAddress().delete())
        for obj in addressList.enumerated() {
            let insert = getTmsAddress().insert(ID <- obj.element.id,province <- obj.element.province,city <- obj.element.city,cityId <- obj.element.cityId,region <- obj.element.region,regionId <- obj.element.regionId,detailAddress <- obj.element.detailAddress,isDefault <- obj.element.isDefault,name <- obj.element.name,phone <- obj.element.phone,phoneCode <- obj.element.phoneCode,postCode <- obj.element.postCode,postCode <- obj.element.postCode,status <- obj.element.status,updateTime <- obj.element.updateTime,modifTimer <- obj.element.modifTimer)
            if (try? getDB().run(insert)) != nil {
                print("tmsAddress插入成功")
            } else {
                print("tmsAddress插入失败")

            }
        }
    }
   
    let goodName = Expression<String>("goodName")
    
    func addGoodsType(goodsType:Array<JSON>) {
        for obj in goodsType.enumerated() {
            let insert = getTmsGoodsType().insert(goodName <- obj.element.description)
            if (try? getDB().run(insert)) != nil {
                print("getTmsGoodsType插入成功")
            } else {
                print("getTmsGoodsType插入失败")

            }
        }
    }
    
    func getGoodsType() -> Array<String> {
        var dataAry = [String]()
        for list in (try! getDB().prepare(getTmsGoodsType())) {
            dataAry.append(list[goodName])
        }
        return dataAry
    }
    
    func getTmsGoodsType() -> Table {
        if tmsGoodsType == nil {
            tmsGoodsType = Table("tmsGoodsType")
            try! getDB().run(getTmsGoodsType().create(temporary: false, ifNotExists: true, withoutRowid: false, block: { (tab) in
                tab.column(goodName,primaryKey: true)
            }))
        }
        return tmsGoodsType!;
    }
    
    func removeGoodsType() {
        try! getDB().run(getTmsGoodsType().delete())
    }

    //添加选项表
    func addOptiontWith(item: OptionModel) {
        let insert = getTmsOption().insert(key <- item.key,loginAccount <- item.loginAccount,value <- item.value)
        if (try? getDB().run(insert)) != nil {
            print("getTmsOption插入成功")
        } else {
            print("getTmsOption插入失败")

        }
    }
    
    //插入 CountryCode
    func addCountryCodes(countryModes:Array<WLCountryCode>) {
        try! getDB().run(getTmsCountryCode().delete())
        for obj in countryModes.enumerated() {
            let insert = getTmsCountryCode().insert(nicename <- obj.element.nicename,phonecode <- obj.element.phonecode)
            if (try? getDB().run(insert)) != nil {
                print("TmsCountryCode插入成功")
            } else {
                print("TmsCountryCode插入失败")

            }
        }
    }
    
    func getCommonAreas() -> Array<WLCountryCode> {
        var dataAry = [WLCountryCode]()
        for list in (try! getDB().prepare(getTmsCountryCode().filter(nicename == "China" || nicename == "Myanmar"))) {
            let countryCode:WLCountryCode = WLCountryCode()
            countryCode.nicename = list[nicename]
            countryCode.phonecode = list[phonecode]
            if (countryCode.phonecode == "+86" || countryCode.phonecode == "+95") {
                dataAry.insert(countryCode, at: 0)
            } else {
                dataAry.append(countryCode)
            }
        }
        return dataAry
    }
    
    //查询 CountryCode
    func searchCountryCodes() -> Array<WLCountryCode> {
        var dataAry = [WLCountryCode]()
        for list in (try! getDB().prepare(getTmsCountryCode())) {
            let countryCode:WLCountryCode = WLCountryCode()
            countryCode.nicename = list[nicename]
            countryCode.phonecode = list[phonecode]
            if (countryCode.phonecode == "+86" || countryCode.phonecode == "+95") {
                dataAry.insert(countryCode, at: 0)
            } else {
                dataAry.append(countryCode)
            }
        }
        return dataAry
    }
    
    //插入 Area
    func addAreaList(areaModes:Array<WLAreaModel>) {
        //添加之前 先删除所有
        try! getDB().run(getTmsArea().delete())
        try! getDB().run(getTmsCity().delete())

        for obj in areaModes.enumerated() {
            let insert = getTmsArea().insert(id <- obj.element.id,name <- obj.element.name)
            if (try? getDB().run(insert)) != nil {
                print("TmsArea插入成功")
            } else {
                print("TmsArea插入失败")
            }
            
            for objArea in obj.element.region!.enumerated() {
                let insert = getTmsCity().insert(id <- objArea.element.id,name <- objArea.element.name,parentID <- objArea.element.parentID,parentName <- objArea.element.parentName)
                if (try? getDB().run(insert)) != nil {
                    print("TmsCity插入成功")
                } else {
                    print("TmsCity插入失败")
                }
            }
        }
    }
    
    //查询 AreaList
    func searchAreaList() -> Array<WLAreaModel> {
        var dataAry = [WLAreaModel]()
        for list in (try! getDB().prepare(getTmsArea())) {
            let areaModel:WLAreaModel = WLAreaModel()
            areaModel.id = list[id]
            areaModel.name = list[name]
            
            var cityDataAry = [WLCityModel]()
            for cityList in (try! getDB().prepare(getTmsCity().filter(parentID == id))) {
                
                let cityModel:WLCityModel = WLCityModel()

                cityModel.id = cityList[id]
                cityModel.name = cityList[name]
                cityModel.parentID = cityList[parentID]
                cityModel.parentName = cityList[parentName]
                
                cityDataAry.append(cityModel)

            }
            areaModel.region = cityDataAry
            dataAry.append(areaModel)
        }
        return dataAry
    }
    
}
